/**
 * AdminProjectListDAO class.
 */
package pits.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pits.dto.AdminProjectListDTO;
import pits.util.Constants;
import pits.util.Logger;

public class AdminProjectListDAO {

	public List<AdminProjectListDTO> getData() {

		List<AdminProjectListDTO> result = new ArrayList<AdminProjectListDTO>();
		AdminProjectListDTO runnerDTO = null;

		Connection conn = null;
		String query = Constants.BLANK_VALUE;
		PreparedStatement sqlStatement = null;

		ResultSet projectResultSet = null;
		ResultSet departResultSet = null;
		ResultSet langResultSet = null;
		ResultSet DBResultSet = null;
		ResultSet frameResultSet = null;
		ResultSet techResultSet = null;

		String languages = null;
		String DBMS = null;
		String frameTech = null;

		try {
			conn = pits.util.Connection.getConnection();

			query = "SELECT * FROM project WHERE isDeleted=0";
			// new Logger().writeln(query);
			sqlStatement = conn.prepareStatement(query);
			projectResultSet = sqlStatement.executeQuery();

			while (projectResultSet.next()) {

				runnerDTO = new AdminProjectListDTO();

				query = "SELECT * FROM department WHERE DepartID=" + projectResultSet.getInt("DepartID")
				        + " AND isDeleted=0";
				// new Logger().writeln(query);
				sqlStatement = conn.prepareStatement(query);
				departResultSet = sqlStatement.executeQuery();
				departResultSet.next();

				query = "SELECT * FROM language l, project_language pl" + " WHERE pl.ProjectID="
				        + projectResultSet.getInt("ProjectID") + " AND l.LangID=pl.LangID" + " AND l.isDeleted=0";
				// new Logger().writeln(query);
				sqlStatement = conn.prepareStatement(query);
				langResultSet = sqlStatement.executeQuery();
				languages = "-";
				while (langResultSet.next()) {
					languages += ", " + langResultSet.getString("Name");
					// new Logger().write(languages);
				}
				if (!languages.equals("-")) {
					languages = languages.substring(2);
				}

				// new Logger().write(languages);

				query = "SELECT * FROM pits_db.database d, project_database pd" + " WHERE pd.ProjectID="
				        + projectResultSet.getInt("ProjectID") + " AND d.DatabaseID=pd.DatabaseID"
				        + " AND d.isDeleted=0";
				// new Logger().writeln(query);
				sqlStatement = conn.prepareStatement(query);
				DBResultSet = sqlStatement.executeQuery();
				DBMS = "-";
				while (DBResultSet.next()) {
					DBMS += ", " + DBResultSet.getString("Name");
				}
				if (!DBMS.equals("-")) {
					DBMS = DBMS.substring(2);
				}

				query = "SELECT * FROM framework f, project_framework pf" + " WHERE pf.ProjectID="
				        + projectResultSet.getInt("ProjectID") + " AND f.FrameID=pf.FrameID" + " AND f.isDeleted=0";
				// new Logger().writeln(query);
				sqlStatement = conn.prepareStatement(query);
				frameResultSet = sqlStatement.executeQuery();
				frameTech = "-";
				while (frameResultSet.next()) {
					frameTech += ", " + frameResultSet.getString("Name");
				}

				query = "SELECT * FROM technology t, project_technology pt" + " WHERE pt.ProjectID="
				        + projectResultSet.getInt("ProjectID") + " AND t.TechID=pt.TechID" + " AND t.isDeleted=0";
				// new Logger().writeln(query);
				sqlStatement = conn.prepareStatement(query);
				techResultSet = sqlStatement.executeQuery();
				while (techResultSet.next()) {
					frameTech += ", " + techResultSet.getString("Name");
				}
				if (!frameTech.equals("-")) {
					frameTech = frameTech.substring(2);
				}

				runnerDTO.setName(projectResultSet.getString("ProjectName"));
				runnerDTO.setDepartName(departResultSet.getString("DepartName"));
				runnerDTO.setStartDate(projectResultSet.getString("PeriodFrom"));
				runnerDTO.setEndDate(projectResultSet.getString("PeriodTo"));
				runnerDTO.setDescription(projectResultSet.getString("Description"));
				runnerDTO.setLanguage(languages);
				runnerDTO.setFrameTech(frameTech);
				runnerDTO.setDBMS(DBMS);
				runnerDTO.setProjectID(projectResultSet.getInt("ProjectID"));

				result.add(runnerDTO);
			}
		} catch (SQLException sqlEx) {
			new Logger().writeln(sqlEx.toString());
		} finally {
			try {
				// projectexpResultSet.close();
				// projectResultSet.close();
				// departResultSet.close();
				// langResultSet.close();
				// DBResultSet.close();
				// frameResultSet.close();
				// techResultSet.close();
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new Logger().writeln(sqlEx.toString());
			}
		}
		return result;
	}
}
